using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VKDVISIT
    {
        public DataRow Convert2KDVISIT(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryKDVISIT(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                    #region=========
         dwReturn["KDVIMANDANT"] = " ".PadRight(2 ); 
         dwReturn["KDVIKONTAKT"] = " ".PadRight(8 ); 
         dwReturn["KDVIAENZL"] = " ".PadRight(0 ); 
         dwReturn["KDVIAENB"] = " ".PadRight(8 ); 
         dwReturn["KDVIAEND"] = " ".PadRight(0 ); 
         dwReturn["KDVINEUB"] = " ".PadRight(8 ); 
         dwReturn["KDVINEUD"] = " ".PadRight(0 ); 
         dwReturn["KDVIBEZ"] = " ".PadRight(50 ); 
         dwReturn["KDVIART"] = " ".PadRight(2 ); 
         dwReturn["KDVIDATVON"] = " ".PadRight(0 ); 
         dwReturn["KDVIIDATUM"] = " ".PadRight(0 ); 
         dwReturn["KDVIUHRVON"] = " ".PadRight(0 ); 
         dwReturn["KDVIDATBIS"] = " ".PadRight(0 ); 
         dwReturn["KDVIUHRBIS"] = " ".PadRight(0 ); 
         dwReturn["KDVIORT"] = " ".PadRight(20 ); 
         dwReturn["KDVIKUNDNR"] = " ".PadRight(8 ); 
         dwReturn["KDVIVERTNR"] = " ".PadRight(8 ); 
         dwReturn["KDVIVB"] = " ".PadRight(3 ); 
         dwReturn["KDVIVG"] = " ".PadRight(3 ); 
         dwReturn["KDVILAND"] = " ".PadRight(3 ); 
         dwReturn["KDVIPROJNR"] = " ".PadRight(5 ); 
         dwReturn["KDVIINIT"] = " ".PadRight(1 ); 
         dwReturn["KDVIGRD"] = " ".PadRight(2 ); 
         dwReturn["KDVISTATUS"] = " ".PadRight(1 ); 
         dwReturn["KDVIERG"] = " ".PadRight(2 ); 
         dwReturn["KDVIERGGRD"] = " ".PadRight(2 ); 
         dwReturn["KDVIAKTIV"] = " ".PadRight(8 ); 
         dwReturn["KDVIKAT1"] = " ".PadRight(3 ); 
         dwReturn["KDVIKAT2"] = " ".PadRight(3 ); 
         dwReturn["KDVIKAT3"] = " ".PadRight(3 ); 
         dwReturn["KDVIKAT4"] = " ".PadRight(3 ); 
         dwReturn["KDVITERMIN"] = " ".PadRight(0 ); 
         dwReturn["KDVIZEIT"] = " ".PadRight(0 ); 
         dwReturn["KDVIFILLER"] = " ".PadRight(112 ); 
                    #endregion
                    break;
                case "JLT":

                    break;
                default:

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }
        public void MapKDVISIT(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveKDVISIT(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryKDVISIT(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapKDVISIT(ref dwLocal, dwData);

            if (!flgExist)
            {

                InsertKDVISIT(dwLocal, ITXDBVERSION);
            }
            UpdateKDVISIT(dwLocal);
        }
        public void InsertKDVISIT(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "YGR":
                    or1.CommandText = "INSERT INTO KDVISIT(VAKBMANDANT, VAKBKOLL, VAKBART, VAKBKUNDNR, VAKBFARBE, VAKBKDARTNR, VAKBKDARTBEZ, VAKBKDFARBNR, VAKBANDRUCK, VAKBMEGEW, VAKBMENGEH, VAKBGEWVON, VAKBGEWBIS, VAKBGEWCA, VAKBGEWFKT, VAKBGEWGRP, VAKBMEGFKT, VAKBMEGGRP, VAKBAENDZL, VAKBAENDBEN, VAKBAENDDAT, VAKBNEUBEN, VAKBNEUDAT, VAKBFILLER)"
                        + " VALUES (:VAKBMANDANT, :VAKBKOLL, :VAKBART, :VAKBKUNDNR, :VAKBFARBE, '                         ', '                         ', '                             ', ' ', '   ', '   ', 0, 0, ' ', '000000000000', ' ', '000000000000', ' ', 0, '        ', '000000', '        ', '      ', '                                          ')";
                    or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                //VAFMANDANT VAFKOLL VAFART VAFFARBE VAFBEZ VAFFFARBE

            }
            
            Connect2Proxy(or1);
        }
        public DataSet QueryKDVISIT(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM KDVISIT WHERE  ROWNUM<2 ";
                    break;
              
                case 99:
                    or1.CommandText = "SELECT * FROM KDVISIT  WHERE ROWNUM<50";
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "KDVISIT";
            Return = Result;
            return Return;
        }
        public void DeleteKDVISIT(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM KDVISIT   WHERE  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                     or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        } 
      
        public void UpdateKDVISIT(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    #region====Command==== 
                    or1.CommandText = "UPDATE KDVISIT SET "
                    +"KDVIMANDANT= :KDVIMANDANT,"
                    +"KDVIKONTAKT= :KDVIKONTAKT,"
                    +"KDVIAENZL= :KDVIAENZL,"
                    +"KDVIAENB= :KDVIAENB,"
                    +"KDVIAEND= :KDVIAEND,"
                    +"KDVINEUB= :KDVINEUB,"
                    +"KDVINEUD= :KDVINEUD,"
                    +"KDVIBEZ= :KDVIBEZ,"
                    +"KDVIART= :KDVIART,"
                    +"KDVIDATVON= :KDVIDATVON,"
                    +"KDVIIDATUM= :KDVIIDATUM,"
                    +"KDVIUHRVON= :KDVIUHRVON,"
                    +"KDVIDATBIS= :KDVIDATBIS,"
                    +"KDVIUHRBIS= :KDVIUHRBIS,"
                    +"KDVIORT= :KDVIORT,"
                    +"KDVIKUNDNR= :KDVIKUNDNR,"
                    +"KDVIVERTNR= :KDVIVERTNR,"
                    +"KDVIVB= :KDVIVB,"
                    +"KDVIVG= :KDVIVG,"
                    +"KDVILAND= :KDVILAND,"
                    +"KDVIPROJNR= :KDVIPROJNR,"
                    +"KDVIINIT= :KDVIINIT,"
                    +"KDVIGRD= :KDVIGRD,"
                    +"KDVISTATUS= :KDVISTATUS,"
                    +"KDVIERG= :KDVIERG,"
                    +"KDVIERGGRD= :KDVIERGGRD,"
                    +"KDVIAKTIV= :KDVIAKTIV,"
                    +"KDVIKAT1= :KDVIKAT1,"
                    +"KDVIKAT2= :KDVIKAT2,"
                    +"KDVIKAT3= :KDVIKAT3,"
                    +"KDVIKAT4= :KDVIKAT4,"
                    +"KDVITERMIN= :KDVITERMIN,"
                    +"KDVIZEIT= :KDVIZEIT,"
                    +"KDVIFILLER= :KDVIFILLER,"
                        + " WHERE  "
                        + "  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                    #endregion

                    #region====Parameters====
          or1.Parameters.Add("KDVIMANDANT", OracleType.NVarChar).Value = dwData["KDVIMANDANT"].ToString(); 
          or1.Parameters.Add("KDVIKONTAKT", OracleType.NVarChar).Value = dwData["KDVIKONTAKT"].ToString(); 
          or1.Parameters.Add("KDVIAENZL", OracleType.NVarChar).Value = dwData["KDVIAENZL"].ToString(); 
          or1.Parameters.Add("KDVIAENB", OracleType.NVarChar).Value = dwData["KDVIAENB"].ToString(); 
          or1.Parameters.Add("KDVIAEND", OracleType.NVarChar).Value = dwData["KDVIAEND"].ToString(); 
          or1.Parameters.Add("KDVINEUB", OracleType.NVarChar).Value = dwData["KDVINEUB"].ToString(); 
          or1.Parameters.Add("KDVINEUD", OracleType.NVarChar).Value = dwData["KDVINEUD"].ToString(); 
          or1.Parameters.Add("KDVIBEZ", OracleType.NVarChar).Value = dwData["KDVIBEZ"].ToString(); 
          or1.Parameters.Add("KDVIART", OracleType.NVarChar).Value = dwData["KDVIART"].ToString(); 
          or1.Parameters.Add("KDVIDATVON", OracleType.NVarChar).Value = dwData["KDVIDATVON"].ToString(); 
          or1.Parameters.Add("KDVIIDATUM", OracleType.NVarChar).Value = dwData["KDVIIDATUM"].ToString(); 
          or1.Parameters.Add("KDVIUHRVON", OracleType.NVarChar).Value = dwData["KDVIUHRVON"].ToString(); 
          or1.Parameters.Add("KDVIDATBIS", OracleType.NVarChar).Value = dwData["KDVIDATBIS"].ToString(); 
          or1.Parameters.Add("KDVIUHRBIS", OracleType.NVarChar).Value = dwData["KDVIUHRBIS"].ToString(); 
          or1.Parameters.Add("KDVIORT", OracleType.NVarChar).Value = dwData["KDVIORT"].ToString(); 
          or1.Parameters.Add("KDVIKUNDNR", OracleType.NVarChar).Value = dwData["KDVIKUNDNR"].ToString(); 
          or1.Parameters.Add("KDVIVERTNR", OracleType.NVarChar).Value = dwData["KDVIVERTNR"].ToString(); 
          or1.Parameters.Add("KDVIVB", OracleType.NVarChar).Value = dwData["KDVIVB"].ToString(); 
          or1.Parameters.Add("KDVIVG", OracleType.NVarChar).Value = dwData["KDVIVG"].ToString(); 
          or1.Parameters.Add("KDVILAND", OracleType.NVarChar).Value = dwData["KDVILAND"].ToString(); 
          or1.Parameters.Add("KDVIPROJNR", OracleType.NVarChar).Value = dwData["KDVIPROJNR"].ToString(); 
          or1.Parameters.Add("KDVIINIT", OracleType.NVarChar).Value = dwData["KDVIINIT"].ToString(); 
          or1.Parameters.Add("KDVIGRD", OracleType.NVarChar).Value = dwData["KDVIGRD"].ToString(); 
          or1.Parameters.Add("KDVISTATUS", OracleType.NVarChar).Value = dwData["KDVISTATUS"].ToString(); 
          or1.Parameters.Add("KDVIERG", OracleType.NVarChar).Value = dwData["KDVIERG"].ToString(); 
          or1.Parameters.Add("KDVIERGGRD", OracleType.NVarChar).Value = dwData["KDVIERGGRD"].ToString(); 
          or1.Parameters.Add("KDVIAKTIV", OracleType.NVarChar).Value = dwData["KDVIAKTIV"].ToString(); 
          or1.Parameters.Add("KDVIKAT1", OracleType.NVarChar).Value = dwData["KDVIKAT1"].ToString(); 
          or1.Parameters.Add("KDVIKAT2", OracleType.NVarChar).Value = dwData["KDVIKAT2"].ToString(); 
          or1.Parameters.Add("KDVIKAT3", OracleType.NVarChar).Value = dwData["KDVIKAT3"].ToString(); 
          or1.Parameters.Add("KDVIKAT4", OracleType.NVarChar).Value = dwData["KDVIKAT4"].ToString(); 
          or1.Parameters.Add("KDVITERMIN", OracleType.NVarChar).Value = dwData["KDVITERMIN"].ToString(); 
          or1.Parameters.Add("KDVIZEIT", OracleType.NVarChar).Value = dwData["KDVIZEIT"].ToString(); 
          or1.Parameters.Add("KDVIFILLER", OracleType.NVarChar).Value = dwData["KDVIFILLER"].ToString(); 
                    #endregion
                    break; 
                default:
                    break;
            }


            Connect2Proxy(or1);
        }
    }
}
